<?php
namespace app\lib;

class Excel {
    /*
     *$expTitle 标题
     * $expCellName 表头
     *
     **/
    public static function exportExcel($expTitle, $expCellName, $expTableData=[], $expOrders = 0){
        $expTitleLen = count($expCellName);
        if( 0 === $expTitleLen ) {
            exit('异常');
        }
        $cellNum = $expOrders ? $expTitleLen - 1 : $expTitleLen;
        $dataNum = count($expTableData);
        $expOrders && $extNum = count($expOrders);
        vendor("PHPExcel.PHPExcel");

        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        /*设置文件基本属性*/
        $objProps->setTitle("Office XLS Test Document");
        $objPHPExcel->getActiveSheet()->mergeCells('A1:' .self::IntToChr($cellNum - 1) . '1');//标题
        /*
         * 列标题
         * */
        $cellName = [];
        for ($i = 0; $i < $cellNum; $i++) {
            $key = isset($cellName[$i]) ?$cellName[$i]:self::IntToChr($i);
            $objPHPExcel->getActiveSheet()->mergeCells($key . '1:' . $key . '3');//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($key . '1', $expCellName[$i][1]);
            $objPHPExcel->getActiveSheet()->getColumnDimension($key)->setWidth(22);
        }
        // Miscellaneous glyphs, UTF-8
        /*
         * 列值*/
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $key = isset($cellName[$j]) ?$cellName[$i]:self::IntToChr($j);
                $objPHPExcel->getActiveSheet()->setCellValue($key.($i + 4),'`'.$expTableData[$i][$expCellName[$j][0]]);
            }
        }

        $objPHPExcel->getActiveSheet(0)->setCellValue('A1', $expTitle);
        ob_end_clean();//清除缓冲区,避免乱码
        header('pragma:public');
        header("Content-type:application/vnd.ms-excel.;charset=UTF-8");
        header("Content-Disposition:attachment;filename=$expTitle.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public static function exportExcelAJ($expTitle, $expCellName, $expTableData=[], $expOrders = 0){
        $expTitleLen = count($expCellName);
        if( 0 === $expTitleLen ) {
            exit('异常');
        }
        $cellNum = $expOrders ? $expTitleLen - 1 : $expTitleLen;
        $dataNum = count($expTableData);
        $expOrders && $extNum = count($expOrders);
        vendor("PHPExcel.PHPExcel");

        $objPHPExcel = new \PHPExcel();
        $objProps = $objPHPExcel->getProperties();

        /*设置文件基本属性*/
        $objProps->setTitle("Office XLS Test Document");
        $objPHPExcel->getActiveSheet()->mergeCells('A1:' .self::IntToChr($cellNum) . '1');//标题
        /*
         * 列标题
         * */
        $cellName = [];
        for ($i = 0; $i < $cellNum; $i++) {
            $key = isset($cellName[$i]) ?$cellName[$i]:self::IntToChr($i);
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($key . '2', $expCellName[$i][1]);
            $objPHPExcel->getActiveSheet()->getColumnDimension($key)->setWidth(22);
        }
        // Miscellaneous glyphs, UTF-8
        /*
         * 列值*/
        $count = 3;
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $key = isset($cellName[$j]) ?$cellName[$i]:self::IntToChr($j);
                $objPHPExcel->getActiveSheet()->setCellValue($key.($i + $count),$expTableData[$i][$expCellName[$j][0]]?:'');
            }
        }

        $objPHPExcel->getActiveSheet(0)->setCellValue('A1', $expTitle);
        ob_end_clean();//清除缓冲区,避免乱码
        header('pragma:public');
        header("Content-type:application/vnd.ms-excel.;charset=UTF-8");
        header("Content-Disposition:attachment;filename=$expTitle.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    /*
     * $index 从0开始
     *	@param	string $num
     *	@return	int Column index (base 1 !!!)
     */
    public static function IntToChr($index, $start = 65) {
        $str = '';
        if (floor($index / 26) > 0) {
            $str .= self::IntToChr(floor($index / 26)-1);
        }
        return $str . chr($index % 26 + $start);
    }
}